﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Runtime.Caching;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ComTools
{
    public partial class frmSqlDependency : Form
    {
        public frmSqlDependency()
        {
            InitializeComponent();
        }

        private void frmTableEntity_Load(object sender, EventArgs e)
        {
            timer = new System.Threading.Timer(
                new System.Threading.TimerCallback(TimerCallback),
                null, 1000, System.Threading.Timeout.Infinite
                );
        }

        private void TimerCallback(object state)
        {
            if (dataTables.Count > 0)
            {
                var dataTable = dataTables.Peek();
                foreach (DataRow dr in dataTable.Rows)
                {
                    AddGridView(dr);
                }
                dataTables.Dequeue();
            }
            //
            timer.Change(10, System.Threading.Timeout.Infinite);
        }

        Action<DataRow> delAddGridRow;
        private void AddGridView(DataRow dr)
        {
            if (delAddGridRow == null)
            {
                delAddGridRow = new Action<DataRow>((row) => {
                    dataGridView1.Rows.Add(row[0], row[1], row[2]);
                });
            }
            this.BeginInvoke(delAddGridRow, dr);
        }

        Action<string> actionOutput = null;

        private void Output(string txt)
        {
            if (actionOutput == null)
            {
                actionOutput = new Action<string>(Set2TextBox);
            }

            this.Invoke(actionOutput, txt);
        }

        private void Set2TextBox(string txt)
        {
            textBox1.AppendText(txt);
        }

        private void OutputLine(string txt)
        {
            Output(txt + Environment.NewLine);
        }

        private void btnInit_Click(object sender, EventArgs e)
        {
            connections = new SqlConnection(conn);
            SqlDependency.Start(conn);
        }
        string sql = @"create table dbo.aa(
	id int identity(1,1) primary key clustered not null,
	name varchar(50) null,
	docState int null
)
go";
        SqlConnection connections = null;
        string conn = "Server=.;Database=MES;Integrated Security=true";
        //string sql = "select id, name, docState from [dbo].[aa]";
        long id = 0;
        private void Update()
        {
            try
            {
                string sql = "select id, name, docState from [dbo].[aa] where id>@id";
                //
                if (connections.State != ConnectionState.Open)
                {
                    connections.Open();
                }
                SqlCommand command = new SqlCommand(sql, connections);
                command.CommandType = CommandType.Text;
                command.Parameters.AddWithValue("id", id);
                //
                SqlDependency dependency = new SqlDependency(command);
                dependency.OnChange += new OnChangeEventHandler(Sqld_OnChange);

                DataTable dt = new DataTable(Guid.NewGuid().ToString());
                var reader = command.ExecuteReader();
                dt.Load(reader);
                if (dt.Rows.Count > 0)
                {
                    object obj = dt.Compute("max(id)", "");
                    if (obj != DBNull.Value)
                    {
                        id = Convert.ToInt32(obj);
                    }
                }
                //
                dataTables.Enqueue(dt);
            }
            catch (SqlException sqle)
            {
                OutputLine("第" + sqle.LineNumber.ToString() + "行，错误代号：" + sqle.ErrorCode.ToString());
                OutputLine(sqle.Message);
                OutputLine(sqle.StackTrace);
            }
            //SqlChangeMonitor
        }

        Queue<DataTable> dataTables = new Queue<DataTable>();
        System.Threading.Timer timer = null;

        private void Sqld_OnChange(object sender, SqlNotificationEventArgs e)
        {
            OutputLine(e.Source.ToString() + " " + e.Type.ToString() + ":" + e.Info.ToString());
            //
            if (e.Info == SqlNotificationInfo.Insert)
            {
                Update();
            }
            else if (e.Info == SqlNotificationInfo.Expired)
            {

            }
        }

        private void btnDestroy_Click(object sender, EventArgs e)
        {
            connections.Close();
        }

        private void btnRun_Click(object sender, EventArgs e)
        {
            Update();
        }

        private void frmTableEntity_FormClosing(object sender, FormClosingEventArgs e)
        {
            SqlDependency.Stop(conn);
        }
    }
}